Você é funcionário dos correios da cidade de Boston e, como tal, supervisiona a entrega de correspondência em toda a cidade. Na maior parte, todos os pacotes enviados são eventualmente entregues. Exceto que, de vez em quando, um mistério cai no seu colo: um pacote perdido! Para cada cliente que chega até você com um relatório de pacote perdido, sua tarefa é determinar:
Tudo o que você sabe é o que os próprios clientes lhe dirão. Para
resolver cada mistério, você precisará usar o banco de
dados do serviço de entrega de correio,
packages.db
BAIXAR,
que contém dados sobre o trânsito de pacotes pela cidade. Usando apenas
as informações do banco de dados, sua tarefa é ajudar cada cliente a
encontrar o pacote perdido.
packages.db representa todas as entregas recentes de
pacotes na cidade de Boston. Para fazer isso, packages.db
representa as seguintes entidades:
Dentro de packages.db, você encontrará diversas tabelas.
Clique nos menus abaixo para saber mais sobre o esquema de cada tabela
individual.
A tabela addresses contém as
seguintes colunas:
id: identifica o id do endereçoaddress: o endereço em sitype: tipo de endereço (residencial, comercial,
etc…)A tabela drivers contém as seguintes
colunas:
id: identifica o id do endereçoname: o nome do motoristaA tabela drivers contém as seguintes
colunas:
id: identifica o id da encomenda (pacote)contents: o conteúdo da encomendafrom_address_id: endereço de onde o pacote foi
enviadoto_address_id: o endereço que o pacote deve chegar.
Não necessáriamente é onde ele finaliza!A tabela scans contém as seguintes
colunas:
id: identifica o id do logdriver_id: id do motorista que criou o logpackage_id: endereço do pacote que foi
transportadoaddress_id: id do endereço que foi feito o logaction: indica se o pacote foi coletado (“Picked”) ou
entregue (“Drop”) no endereçotimestamp: indica o dia e hora que o log foi
registradoAgora que você sabe as informações do banco de dados, antes de resolver os problemas dos clientes convém determinar o diagrama ER para os dados.
Crie o diagrama ER para os dados e escreva o nome das colunas de cada tabela ao lado da mesma (retângulos no diagrama). Use algum software para criar o diagrama.
Seu primeiro relato de um pacote desaparecido vem de Anneke. Anneke vai até o seu balcão e diz o seguinte:
Sr(a) atendente, meu nome é Anneke. Eu moro na
900 Somerville Avenue. Não faz muito tempo, enviei uma carta especial (Congratulatory letter). É para meu amigo Varsha. Ela está começando um novo capítulo de sua vida em2 Finnegan Street, na parte alta da cidade. (Esse endereço, deixe-me dizer: foi um pouco complicado acertar na primeira vez.) A carta é uma nota de felicitações – um alegre abraço de papel meu para ela, para comemorar essa grande mudança dela. Você pode verificar se já chegou até ela?
Escreva as queries usadas para responder a pergunta. Tente usar o menor número de queries possíveis para cada resposta.
Seu segundo relato de um pacote desaparecido vem de um sujeito misterioso de fora da cidade. Ele vai até o seu balcão e dizem o seguinte:
Bom dia para você, entregador da correspondência. Você deve se lembrar que não faz muito tempo vim da cidade de Fiftyville. Entreguei uma certa caixa em suas mãos confiáveis e pedi que mantivesse as coisas em segredo. Meu associado já esperava o pacote há algum tempo. E ainda assim, parece ter criado asas e voado para longe. Ha! Alguma chance de você ajudar a esclarecer esse mistério? Receio que não haja endereço “De”. É o tipo de pacote que poderia causar….problemas a algumas pessoas, se é que me entende!
Escreva as queries usadas para responder a pergunta. Tente usar o menor número de queries possíveis para cada resposta.
Seu terceiro relato de desaparecimento de um pacote vem de um avô que mora na mesma rua do correio. Ele se aproxima do seu balcão e diz o seguinte:
Ah, com licença, escriturário. Eu tinha enviado um presente misterioso para minha neta maravilhosa, no endereço
728 de Maple Place. Isso foi há cerca de duas semanas. Agora o prazo do envio já passou de sete dias inteiros e ouvi dizer que ela ainda espera, com as mãos vazias e o coração cheio de expectativas. Estou um pouco preocupado em saber para onde foi meu pacote. Não consigo lembrar o que há dentro, mas sei que está cheio até a borda com meu amor por ela. Podemos rastreá-lo para que possa encher o dia dela de alegria? Enviei-o da minha casa, no enderço109 da Tileston Street.
Escreva as queries usadas para responder a pergunta. Tente usar o menor número de queries possíveis para cada resposta.
Primeiro encontrando os dados do endereço
900 Somerville Avenue:
SELECT * FROM "addresses" WHERE "address" = '900 Somerville Avenue'
Com o id (432) do endereço, podemos buscar as entregas que possuem
esse id como inicio (from_address_id)
SELECT * FROM "packages" WHERE "from_address_id" = 432;
Agora vemos que o pacote Congratulatory letter tem id
384, podemos buscar nos logs (scans) os trâmites desse
pacote:
SELECT * FROM "scans" WHERE "package_id" = 384;
Agora vemos que a última transação do pacote foi uma entrega para o endereço 854. Vejamos qual é este endereço:
SELECT * FROM "addresses" WHERE "id" = 854;
Se juntarmos tudo em uma query:
SELECT "address" FROM "addresses" WHERE "id" = (
SELECT "address_id" FROM "scans" WHERE "package_id" = (
SELECT "id" FROM "packages" WHERE ("contents" = 'Congratulatory letter') AND ("from_address_id" = (
SELECT "id" FROM "addresses" WHERE "address" = '900 Somerville Avenue'))
) ORDER BY "timestamp" DESC LIMIT 1
);
Encontrando uma entrega que não possua remetente
(from_address_id). Nessa query já conseguimos ver o
conteúdo do pacote.
SELECT * FROM "packages" WHERE "from_address_id" IS NULL;
Verificando os scans que tem o id 5098:
SELECT * FROM "scans" WHERE "package_id" = 5098;
Coletando o id do endereço da ultima transação (348), e verificando os dados do mesmo:
SELECT * FROM "addresses" WHERE "id" = 348;
O endereço é uma estação de polícia!
Criando uma query única:
SELECT * FROM "addresses" WHERE "id" =
(
SELECT "address_id" FROM "scans" WHERE "package_id" =
(
SELECT "id" FROM "packages" WHERE "from_address_id" IS NULL
) ORDER BY "timestamp" DESC LIMIT 1
);
Encontrando o último scan que possua o pacote cujo endereço é
109 Tileston Street:
SELECT * FROM "scans" WHERE "package_id" =
(
SELECT "id" FROM "packages" WHERE "from_address_id" =
(
SELECT "id" FROM "addresses" WHERE "address" = '109 Tileston Street'
)
) ORDER BY "timestamp" DESC LIMIT 1;